<?php 
	class Topics extends CI_Model{

	 	function __construct(){
        	parent::__construct();
    	}
    	function get_subject($school_id = 0){
    		$sql = "SELECT s.* FROM {$this->db->dbprefix}b_subject AS s ";
            $sql .= "LEFT JOIN {$this->db->dbprefix}o2o_school_tk_packages AS p ON(p.subject_id=s.id) ";
            $sql .= "WHERE s.school_id = '{$school_id}' OR (p.school_id = '{$school_id}' AND p.status=1)";
    		$result = $this->db->query($sql)->result_array();
    		return $result;
    	}

    	function get_chapter($conditions = array()){
            $where = "WHERE 1=1";
            if(isset($conditions['subject_id'])){
                $where .= " AND subject_id = '{$conditions['subject_id']}'";
            }
    		$sql = "SELECT * FROM {$this->db->dbprefix}b_subject_chapter $where ORDER BY title ASC";
    		$result = $this->db->query($sql)->result_array();
    		return $result;
    	}

        function get_topic_list($limit = 0, $offset = 0, $conditions = array()){
            $where = "1 = 1";
            if(!empty($text)) $where .= " AND subject_text LIKE '%{$text}%'";
            if(count($conditions) > 0){
                if(!empty($conditions['source_type'])){
                    $where .= " AND source_type = '{$conditions['source_type']}'";
                }
                if(!empty($conditions['subject_id'])){
                    $where .= " AND subject_id = '{$conditions['subject_id']}'";
                }
                if(!empty($conditions['chapter_id'])){
                    $where .= " AND chapter_id IN({$conditions['chapter_id']})";
                }
                if(!empty($conditions['type'])){
                    $where .= " AND type = '{$conditions['type']}'";
                }
                if(!empty($conditions['subject_text'])){
                    $where .= " AND subject_text LIKE '%{$conditions['subject_text']}%'";
                }
                
                if(!empty($conditions['difficulty_level'])){
                    $where .= " AND difficulty_level = '{$conditions['difficulty_level']}'";
                }
                if(!empty($conditions['school_id'])){
                    $time = time();
                    // $where .= " AND (school_id = '{$conditions['school_id']}' OR category_id IN(SELECT category_id FROM {$this->db->dbprefix}o2o_school_system_topics WHERE school_id='{$conditions['school_id']}' AND add_time <= $time AND expire_tiem >= $time))";
                    $where .= " AND school_id = '{$conditions['school_id']}' ";
                }

                if(!empty($conditions['st'])){
                    $conditions['st'] = strtotime($conditions['st']);
                    $where .= " AND add_time > {$conditions['st']} ";
                }
                if(!empty($conditions['et'])){
                    $conditions['et'] = strtotime($conditions['et']) + 86400;
                    $where .= " AND add_time < {$conditions['et']} ";
                }
            }
            $sql = "SELECT SQL_CALC_FOUND_ROWS * FROM {$this->db->dbprefix}tk_topics WHERE $where ORDER BY id DESC ";
            if($limit > 0 || $offset > 0){
                $sql .= "LIMIT $limit, $offset";
            }
            $result = $this->db->query($sql)->result_array();
            $row_num =  $this->db->query('SELECT FOUND_ROWS() as row_num;')->row_array();
            return array("count" => $row_num['row_num'], "result" => $result);
        }

        function get_topic_reuslt($topic_id){
            $sql = "SELECT * FROM {$this->db->dbprefix}tk_topic_result WHERE qid = $topic_id ORDER BY id ASC";
            $result = $this->db->query($sql)->result_array();
            return $result;
        }

        function get_test_volume_templates($schoo_id){
            $sql = "SELECT * FROM {$this->db->dbprefix}tk_test_volume_templates WHERE school_id = '{$schoo_id}' ORDER BY id DESC";
            $result = $this->db->query($sql)->result_array();
            return $result;
        }

        function get_rand_topic($conditions = array()){
            $where = "WHERE t1.id >= t2.id ";
            $lwhere = 'WHERE 1=1 ';
            if(!empty($conditions['type'])){
                $where .= "AND t1.type = '{$conditions['type']}' ";  
                $lwhere .= "AND type = '{$conditions['type']}' ";
            }

            if(!empty($conditions['notin_ids'])){
                $notin_ids = implode(",", $conditions['notin_ids']);
                $where .= "AND t1.id NOT IN($notin_ids) ";  
            }

            if(isset($conditions['school_id'])){
                $where .= "AND t1.school_id = '{$conditions['school_id']}' ";  
                $lwhere .= "AND school_id = '{$conditions['school_id']}' ";
            }

            if(!empty($conditions['subject_id'])){
                $where .= "AND t1.subject_id = '{$conditions['subject_id']}' ";
                $lwhere .= "AND subject_id = '{$conditions['subject_id']}' ";
            }
            
            $sql = "SELECT t1.id,t1.subject_text FROM {$this->db->dbprefix}tk_topics AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM {$this->db->dbprefix}tk_topics $lwhere)-(SELECT MIN(id) FROM {$this->db->dbprefix}tk_topics $lwhere))+(SELECT MIN(id) FROM {$this->db->dbprefix}tk_topics $lwhere)) AS id) AS t2 $where ORDER BY t1.id LIMIT 1";
            $result = $this->db->query($sql)->row_array();
            return $result;
        }

        function get_topic_by_id($topic_id){
            $sql = "SELECT id,type,subject_text,analysis,difficulty_level FROM {$this->db->dbprefix}tk_topics WHERE id IN($topic_id)";
            $result = $this->db->query($sql)->result_array();
            return $result;
        }

        function get_topic_result_by_id($topic_id){
            $sql = "SELECT * FROM {$this->db->dbprefix}tk_topic_result WHERE qid IN($topic_id)";
            $result = $this->db->query($sql)->result_array();
            return $result;
        }

        function get_school_system_topics($school_id, $limit = 0, $offset = 10){
            $sql = "SELECT SQL_CALC_FOUND_ROWS st.*,c.name FROM {$this->db->dbprefix}o2o_school_system_topics AS st ";
            $sql .= "LEFT JOIN {$this->db->dbprefix}tk_categorys AS c ON(c.id=st.category_id) ";
            $sql .= "WHERE st.school_id = '{$school_id}' ORDER BY st.id DESC LIMIT $limit, $offset ";
            $result = $this->db->query($sql)->result_array();
            $row_num =  $this->db->query('SELECT FOUND_ROWS() as row_num;')->row_array();
            return array("count" => $row_num['row_num'], "result" => $result);
        }

        function get_school_system_topic_codes($school_id, $category_id, $limit = 0, $offset = 10){
            $sql = "SELECT SQL_CALC_FOUND_ROWS stc.*, u.nick FROM {$this->db->dbprefix}o2o_school_system_topic_codes AS stc ";
            $sql .= "LEFT JOIN {$this->db->dbprefix}user AS u ON(u.id=stc.use_uid) ";
            $sql .= "WHERE stc.school_id = '{$school_id}' AND stc.category_id = '{$category_id}' ORDER BY stc.status ASC, stc.id DESC LIMIT $limit, $offset ";
            $result = $this->db->query($sql)->result_array();
            $row_num =  $this->db->query('SELECT FOUND_ROWS() as row_num;')->row_array();
            return array("count" => $row_num['row_num'], "result" => $result);
        }

        function get_topic_analys($topic_id){
            $sql = "SELECT * FROM {$this->db->dbprefix}tk_topic_analys WHERE qid = $topic_id ORDER BY id ASC ";
            $result = $this->db->query($sql)->result_array();
            return $result;
        }

        function get_topic_analys_result($aid){
            $sql = "SELECT * FROM {$this->db->dbprefix}tk_topic_analys_result WHERE aid = $aid ORDER BY id ASC ";
            $result = $this->db->query($sql)->result_array();
            return $result;
        }

        function get_analys_list($type = 3, $limit = 0, $offset = 10, $conditions = array()){
            $where = "v.school_id = '{$this->user_data['school_id']}' AND vp.type='{$type}' ";
            if(!empty($conditions['subject_id'])){
                $where .= " AND s.id = '{$conditions['subject_id']}' ";
            }

            if(!empty($conditions['name'])){
                $where .= " AND v.name LIKE '%{$conditions['name']}%' ";
            }
            if(!empty($conditions['start_time'])){
              $start_time = strtotime($conditions['start_time']);
              $where .= " AND vp.add_time >= '{$start_time}' ";
            }
            if(!empty($conditions['end_time'])){
              $end_time = strtotime($conditions['end_time']);
              $where .= " AND vp.add_time <= '{$end_time}' ";
            }
            $sql = "SELECT SQL_CALC_FOUND_ROWS v.*, s.title, vp.classes,vp.timeout FROM {$this->db->dbprefix}tk_test_volumes_publish AS vp ";
            $sql .= "LEFT JOIN {$this->db->dbprefix}tk_test_volumes AS v ON(v.id = vp.volume_id) ";
            $sql .= "LEFT JOIN {$this->db->dbprefix}b_subject AS s ON(s.id=v.subject_id) ";
            if($this->is_manage==0){
                // $where .= " and lecturer_id = {$this->user_data['teacher_id']}";
                $sql .= "LEFT JOIN {$this->db->dbprefix}o2o_class AS oc ON(oc.lecturer_id={$this->user_data['teacher_id']}) ";
                // $where .= " AND oc.id > 0 ";
                $where .= " AND FIND_IN_SET(oc.id, vp.classes) ";
            }else{
                if(!empty($conditions['class_id'])){
                    $where .= " AND FIND_IN_SET('{$conditions['class_id']}', vp.classes) ";
                }
            }
            $sql .= "WHERE $where LIMIT $limit, $offset";
            $result = $this->db->query($sql)->result_array();
            // print_r($result);exit;
            $row_num =  $this->db->query('SELECT FOUND_ROWS() AS row_num;')->row_array();
            return array("count" => $row_num['row_num'], "result" => $result);
        }

        function get_analys_volume($limit = 0, $offset = 10, $conditions = array()){
            $where = "v.school_id = '{$this->user_data['school_id']}' ";
            if(!empty($conditions['uid'])){
                $where .= " AND u.id = '{$conditions['uid']}' ";
            }
            if(!empty($conditions['nick'])){
                $where .= " AND sd.name LIKE '%{$conditions['nick']}%' ";
            }
            if(!empty($conditions['title'])){
                $where .= " AND v.name LIKE '%{$conditions['title']}%' ";
            }
            $sql = "SELECT SQL_CALC_FOUND_ROWS s.title,vh.type,vh.volume_id,vh.begin_time,vh.end_time,vh.status,v.name,sd.name AS nick,right_percentage,fraction,u.id AS uid FROM {$this->db->dbprefix}tk_test_volumes AS v ";
            $sql .= "LEFT JOIN {$this->db->dbprefix}tk_test_volume_historys AS vh ON(vh.volume_id=v.id) ";
            $sql .= "LEFT JOIN {$this->db->dbprefix}user AS u ON(u.id = vh.uid) ";
            $sql .= "LEFT JOIN {$this->db->dbprefix}b_subject AS s ON(s.id=v.subject_id) ";
            $sql .= "LEFT JOIN {$this->db->dbprefix}o2o_school_student AS ss ON(ss.user_id=u.id) AND ss.school_id = '{$this->user_data['school_id']}' ";
            $sql .= "LEFT JOIN {$this->db->dbprefix}o2o_student AS sd ON(sd.student_id=ss.student_id) ";
            $sql .= "WHERE $where LIMIT $limit, $offset";
            $result = $this->db->query($sql)->result_array();
            $row_num =  $this->db->query('SELECT FOUND_ROWS() AS row_num;')->row_array();
            return array("count" => $row_num['row_num'], "result" => $result);
        }

        function get_analys_data($conditions = array()){
            $where = 'vh.volume_id <= 0 ';
            if(!empty($conditions['uid'])){
                $where .= " AND vh.uid = '{$conditions['uid']}' ";
            }
            if(!empty($conditions['subject_id'])){
                $where .= " AND s.id = '{$conditions['subject_id']}' ";
            }
            $sql = "SELECT sc1.id AS pid, sc1.title AS p_title ,sc.id AS cid, sc.title, count(vh.id) AS count, count(vh1.id) AS right_count, count(vh2.id) AS wrong_count, vh.subject_id, s.title AS s_title FROM {$this->db->dbprefix}tk_test_topic_historys AS vh ";
            $sql .= "LEFT JOIN {$this->db->dbprefix}tk_test_topic_historys AS vh1 ON(vh1.is_right = 1 AND vh1.id=vh.id) ";
            $sql .= "LEFT JOIN {$this->db->dbprefix}tk_test_topic_historys AS vh2 ON(vh2.is_right = 0 AND vh2.id=vh.id) ";
            $sql .= "LEFT JOIN {$this->db->dbprefix}b_subject_chapter AS sc ON(sc.id=vh.chapter_id) ";
            $sql .= "LEFT JOIN {$this->db->dbprefix}b_subject_chapter AS sc1 ON (sc1.id=sc.parent_id) ";
            $sql .=" LEFT JOIN {$this->db->dbprefix}b_subject AS s ON(s.id=vh.subject_id) ";
            $sql .= "LEFT JOIN {$this->db->dbprefix}user AS u ON(u.id=vh.uid) ";
            $sql .= "WHERE $where GROUP BY vh.chapter_id";
            $result = $this->db->query($sql)->result_array();
            return $result;
        }

        function get_volume_historys_by_id($volume_id, $type, $limit = 0, $offset = 10, $conditions = array()){
            $where = "vh.volume_id = '{$volume_id}' AND vh.type = '{$type}' ";
            if(!empty($conditions['name'])){
                $where .= " AND s.name LIKE '%{$conditions['name']}%' ";
            }
            if(!empty($conditions['class_id'])){
                $where .= " AND class.id = '{$conditions['class_id']}' ";
            }
            // s.name, SUM(th1.fraction) AS zg_fraction, SUM(th2.fraction) AS kg_fraction
            $sql = "SELECT SQL_CALC_FOUND_ROWS vh.*, s.name, class.class_name FROM {$this->db->dbprefix}tk_test_volume_historys AS vh ";
            $sql .= "LEFT JOIN {$this->db->dbprefix}o2o_school_student AS ss ON(ss.user_id = vh.uid) ";
            $sql .= "LEFT JOIN {$this->db->dbprefix}o2o_student AS s ON(ss.student_id = s.student_id) ";
            $sql .= "LEFT JOIN {$this->db->dbprefix}o2o_class_student AS cs ON(cs.student_id = ss.student_id) ";
            $sql .= "LEFT JOIN {$this->db->dbprefix}o2o_class AS class ON(class.id = cs.class_id) ";
            /*$sql .= "LEFT JOIN {$this->db->dbprefix}tk_test_topic_historys AS th1 ON(th1.volume_history_id = vh.id AND vh.type = '{$type}' AND th1.topic_type IN(8,9)) ";
            $sql .= "LEFT JOIN {$this->db->dbprefix}tk_test_topic_historys AS th2 ON(th2.volume_history_id = vh.id AND vh.type = '{$type}' AND th2.topic_type NOT IN(8,9)) ";*/
            $sql .= "WHERE $where GROUP BY vh.id ORDER BY fraction DESC LIMIT $limit, $offset";
            $result = $this->db->query($sql)->result_array();
            $row_num =  $this->db->query('SELECT FOUND_ROWS() AS row_num;')->row_array();
            return array("count" => $row_num['row_num'], "result" => $result);

        }

        function get_volume_historys_ranking($volume_id, $type){
            $sql = "SELECT id,fraction FROM {$this->db->dbprefix}tk_test_volume_historys WHERE volume_id = '{$volume_id}' AND type = '{$type}' ORDER BY fraction DESC";
            $result = $this->db->query($sql)->result_array();
            return $result;
        }

        function get_all_topic($topic_id, $conditions = array()){
            $where = " t.id IN($topic_id) ";
            if(!empty($conditions['chapter_id'])){
                $where .= " AND sc.id='{$conditions['chapter_id']}' ";
            }
            if(!empty($conditions['topic_type'])){
                $where .= " AND t.type='{$conditions['topic_type']}' ";
            }
            $sql = "SELECT t.id,t.type,t.subject_text,sc.title FROM {$this->db->dbprefix}tk_topics AS t ";
            $sql .= "LEFT JOIN {$this->db->dbprefix}b_subject_chapter AS sc ON(sc.id=t.chapter_id) ";
            $sql .= " WHERE $where";
            $result = $this->db->query($sql)->result_array();
            return $result;
        }
	}